const { Service } = require('egg');
const { v4: uuidV4 } = require('uuid');
const moment = require('moment');
const product_table = 'product',
  user_table = 'user',
  file_table = 'file',
  price_table = 'price',
  inventory_table = 'inventory',
  barcode_table = 'barcode';
class ProductService extends Service {

  // 获取产品列表
  async list(name, type, describe, order, method, pageIndex, pageSize) {
    const ctx = this.ctx;
    try {
      let sql = ctx.app.knex().select('t1.uuid', 't1.name', 't1.describe', 't1.create_time',
        't1.update_time', 't1.create_by', 't2.update_by', 't1.is_delete', 't2.name as create_name',
        't3.name as update_name', 't4.url as thumbnail')
        .from(`${product_table} as t1`)
        .leftJoin(`${user_table} as t2`, function() {
          this.on('t1.create_by', '=', 't2.uuid');
        })
        .leftJoin(`${user_table} as t3`, function() {
          this.on('t1.update_by', '=', 't3.uuid');
        })
        .leftJoin(`${file_table} as t4`, function() {
          this.on('t1.file_id', '=', 't4.uuid');
        })
        .whereRaw('t1.is_delete = 0');

      let countSql = ctx.app.knex(product_table).count('uuid as count');

      if (name) {
        sql = sql.andWhereLike('t1.name', `%${name}%`);
        countSql = countSql.andWhereLike('name', `%${name}%`);
      }

      if (type) {
        sql = sql.andWhere({ type });
        countSql = countSql.andWhere({ type });
      }

      if (describe) {
        sql = sql.andWhereLike('t1.describe', `%${describe}%`);
        countSql = countSql.andWhereLike('describe', `%${describe}%`);
      }

      const list = await sql.orderBy(`t1.${order}`, method).limit(Number(pageSize)).offset((Number(pageIndex - 1)) * pageSize);
      const count = await countSql.first();

      return {
        list,
        count: count.count,
      };
    } catch (error) {
      throw error;
    }
  }

  // 新增产品
  async insert(name, describe, type, file_id) {
    const ctx = this.ctx;
    try {
      const { id: user_id } = ctx.session;
      const is_exist = await ctx.app.knex(product_table).select().where({ name })
        .first();
      if (is_exist) {
        throw new Error('名称重复');
      }
      const time = moment();
      const instance = {
        uuid: uuidV4(),
        name, describe, type, file_id,
        is_delete: 0,
        create_time: moment(time).format('YYYY-MM-DD HH:mm:ss'),
        create_by: user_id,
        update_time: moment(time).format('YYYY-MM-DD HH:mm:ss'),
        update_by: user_id,
      };

      await ctx.app.knex(product_table).insert(instance);

      return instance;
    } catch (error) {
      throw error;
    }
  }

  // 修改产品信息
  async update(name, describe, type, file_id, id) {
    const ctx = this.ctx;
    try {
      const { id: user_id } = ctx.session;
      const time = moment();
      if (name) {
        const is_repeat = await ctx.app.knex(product_table).select('*').where({ name })
          .whereNot({ uuid: id })
          .first();
        if (is_repeat) throw new Error('修改名称重复');
      }

      const instance = await ctx.app.knex(product_table)
        .select('*')
        .where({ uuid: id })
        .first();

      if (name) instance.name = name;
      if (describe) instance.describe = describe;
      if (type) instance.type = type;
      if (file_id) instance.file_id = file_id;

      instance.update_time = moment(time).format('YYYY-MM-DD HH:mm:ss');
      instance.update_by = user_id;


      await ctx.app.knex(product_table).update(instance).where({ uuid: id });
      return instance;

    } catch (error) {
      throw error;
    }
  }

  // 删除商品信息
  async delete(ids) {
    const ctx = this.ctx;
    try {
      const time = moment();
      const { id: user_id } = ctx.session;
      const instance = {
        update_time: moment(time).format('YYYY-MM-DD HH:mm:ss'),
        update_by: user_id,
        is_delete: 1,
      };

      await ctx.app.knex(product_table).update(instance).whereIn('uuid', ids);

      return;
    } catch (error) {
      throw error;
    }
  }

  // 查询商品信息
  async info(uuid) {
    const ctx = this.ctx;
    try {
      const time = moment();
      const info = await ctx.app.knex().select('t1.uuid', 't1.name', 't1.describe', 't1.type',
        't1.create_time', 't1.update_time', 't2.name as create_name', 't3.name as update_name'
      )
        .from(`${product_table} as t1`)
        .leftJoin(`${user_table} as t2`, function() {
          this.on('t1.create_by', '=', 't2.uuid');
        })
        .leftJoin(`${user_table} as t3`, function() {
          this.on('t1.update_by', '=', 't3.uuid');
        })
        .whereRaw(`t1.uuid = '${uuid}'`)
        .first();


      // 处理价格
      const prices = await ctx.app.knex().select('price', 'remark')
        .from(price_table)
        .whereRaw(`is_delete =  0 and product_id = '${uuid}' and (end_time is null or end_time > '${moment(time).format('YYYY-MM-DD HH:mm:ss')}')`);

      info.prices = prices;

      // 处理商品数量
      const total_product = await ctx.app.knex.raw(`select m.* from (
          select t.type, SUM(t.number) as value from (
            select t1.type, t1.number from ${inventory_table} as t1 
            left join ${barcode_table} as t2 on t2.uuid = t1.barcode_id and t2.is_delete = 0 
            where t2.product_id = '${uuid}'
          ) as t group by t.type
        ) as m `);

      const in_product = total_product.find(f => {
        return f.type === 0;
      });

      const out_product = total_product.find(f => {
        return f.type === 0;
      });

      const total = {
        in: 0,
        out: 0,
        total: 0,
      };

      total.in = in_product ? in_product.value : 0;

      total.out = out_product ? out_product.value : 0;

      total.total = total.in - total.out;

      info.total = total;

      return info;
    } catch (error) {
      throw error;
    }
  }

}

module.exports = ProductService;
